﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Benchmark_System {
    class Quries {

        public static List<DBOwner> getAllUsers() {
            using (var dbContex = new Model1Container()) {
                var owners = from Owner in dbContex.DBOwnerSet
                             select Owner;
                List<DBOwner> list = new List<DBOwner>();
                foreach (DBOwner owner in owners) {
                    list.Add(owner);
                }
                return list;
            }
        }

        public List<DBJob> GetJobsFromUser(int userId) {
            using (var dbContex = new Model1Container()) {
                var jobs = from job in dbContex.DBJobSet
                           where job.DBOwner.Id == userId
                           select job;
                List<DBJob> list = new List<DBJob>();
                foreach (DBJob job in jobs) {
                    list.Add(job);
                }
                return list;
            }
        }

        public List<DBJob> GetJobsFromUserWithinPastDays(int userId, int daysSince) {
            DateTime dateNow = DateTime.Now.Date;
            DateTime dateAdjusted = dateNow.AddDays(-daysSince);
            using (var dbContex = new Model1Container()) {
                var jobs = from job in dbContex.DBJobSet
                           where job.DBOwner.Id == userId
                           where job.DBLogger.Submitted.CompareTo(dateAdjusted) > 0
                           select job;
                List<DBJob> list = new List<DBJob>();
                foreach (DBJob job in jobs) {
                    list.Add(job);
                }
                return list;
            }
        }


        public List<DBJob> GetJobsFromUserWithinGivenTime(int userId, DateTime start, DateTime end) {
            using (var dbContex = new Model1Container()) {
                var jobs = from job in dbContex.DBJobSet
                           where job.DBOwner.Id == userId
                           where job.DBLogger.Submitted.CompareTo(start) >= 0
                           where job.DBLogger.Submitted.CompareTo(end) <= 0
                           select job;
                List<DBJob> list = new List<DBJob>();
                foreach (DBJob job in jobs) {
                    list.Add(job);
                }
                return list;
            }
        }

         
        public void GetJobsWithinGivenTimeGroupByStatus(DateTime start, DateTime end) {
            using (var dbContex = new Model1Container()) {
                var jobGroups = from job in dbContex.DBJobSet
                                where job.DBLogger.Submitted.CompareTo(start) >= 0
                                where job.DBLogger.Submitted.CompareTo(end) <= 0
                                group job by job.DBLogger into g
                                select new { Status = g.Key, Jobs = g };

                foreach (var g in jobGroups) {
                    Console.WriteLine("Jobs with status " + g.Status);
                    foreach (var j in g.Jobs) {
                        Console.WriteLine(j);
                    }
                }
            }
        }

        public void GetJobsFromUserWithinGivenTimeGroupByStatus(int userId,DateTime start, DateTime end) {
            using (var dbContex = new Model1Container()) {
                var jobGroups = from job in dbContex.DBJobSet
                                where job.DBOwner.Id == userId
                                where job.DBLogger.Submitted.CompareTo(start) >= 0
                                where job.DBLogger.Submitted.CompareTo(end) <= 0
                                group job by job.DBLogger into g
                                select new { Status = g.Key, Jobs = g };

                foreach (var g in jobGroups) {
                    Console.WriteLine("Jobs with status " + g.Status);
                    foreach (var j in g.Jobs) {
                        Console.WriteLine(j);
                    }
                }
            }
        }
    }
}
